package com.assignment.database;

import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.imageio.ImageIO;
import javax.swing.ImageIcon;
import javax.swing.JOptionPane;
import com.assignment.main.Item;
import com.assignment.main.Supplier;

/**
 * Abstract class holding static methods to perform operations and queries
 * specifically on the Chesney's Fireplaces Database, utilising the
 * DatabaseHandler class.
 * 
 * @author John Wright and Luke Williams
 * 
 */
public abstract class DBOperations {

	/**
	 * Checks the Users table of the database to find out whether the given
	 * username and password combination entered in the login screen is valid.
	 * 
	 * @param name
	 *            The username the user has entered
	 * @param pass
	 *            The password the user has entered
	 * @return Whether the username/password combination exists, and therefore
	 *         whether to allow access to the software
	 */
	public static boolean checkUserCredentials(String name, String pass) {

		DatabaseHandler.connect();

		String query = String.format(
				"select * from Users where username='%s' and password='%s';",
				name, pass);

		String[][] result = DatabaseHandler.returnQuery(query);
		DatabaseHandler.close();

		if (result[0].length > 0) {
			// User exists, allow access
			return true;
		}
		// Deny Access
		return false;

	}

	/**
	 * Retrieves all items from the database of either the fireplace type or
	 * stove type from the database. First a query is performed, then an array
	 * of item objects are built from that query, which is the return value.
	 * 
	 * @param type
	 *            The item type (0=Fireplace, 1=Stove)
	 * @return All items of the given type
	 */
	public static Item[] getItems(int type) {
		DatabaseHandler.connect();

		String[][] result = DatabaseHandler.returnQueryNoImage(String.format(
				"select * from Item where Item_Type='%d';", type));
		DatabaseHandler.printQuery(result);
		Item items[] = new Item[result[0].length];
		String data[] = new String[result.length];

		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {

				data[x] = result[x][y];

			}
			items[y] = new Item(data);
			items[y].setType(type);
			try {
				items[y].setImage(getImage(items[y].getId()));
			} catch (Exception e) {
			}
			;
		}
		DatabaseHandler.close();
		return items;
	}

	/**
	 * Retrieves all Suppliers from the database as an array
	 * 
	 * @return All Suppliers found within the database.
	 */
	public static Supplier[] getSuppliers() {
		DatabaseHandler.connect();

		String[][] result = DatabaseHandler.returnQuery(String
				.format("select * from Supplier;"));
		Supplier suppliers[] = new Supplier[result[0].length];
		String data[] = new String[result.length];
		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {
				data[x] = result[x][y];
			}
			suppliers[y] = new Supplier(data);
		}
		DatabaseHandler.close();
		return suppliers;
	}

	/**
	 * Saves the given Item into the database, performing an insert operation if
	 * it is a new item, or an update operation if the item already exists in
	 * the database and has been amended
	 * 
	 * @param item
	 *            The Item object to be saved
	 * @param isNew
	 *            whether the Item is new to the database
	 * @return Whether successful
	 */
	public static boolean saveItem(Item item, boolean isNew) {
		DatabaseHandler.connect();
		try {
			if (isNew) {
				String query = String
						.format("insert into item values(null,'%s','%s','%d','%d','%.2f','%.2f','%s',null,'%s');",
								item.getName(), item.getDescription(),
								item.getType(), item.getStock(),
								item.getBuyPrice(), item.getSellPrice(),
								item.getStyle(), item.getSupplierId());
				DatabaseHandler.doSQL(query);
				insertImage("" + (getItems(item.getType()).length - 1),
						item.getFile());
			} else {
				System.out.println("style: " + item.getStyle() + "supplier: "
						+ item.getSupplierId());
				String query = String
						.format("update item set item_name='%s',item_description='%s',item_type='%d',Stock_Quantity='%d'"
								+ ",buy_price='%.2f',sell_price='%.2f',style='%d',supplierid='%s' where item.pk_item_id='%s';",
								item.getName(), item.getDescription(),
								item.getType(), item.getStock(),
								item.getBuyPrice(), item.getSellPrice(),
								item.getStyle(), item.getSupplierId(),
								item.getId());
				DatabaseHandler.doSQL(query);

				if (item.getFile() != null)
					insertImage(item.getId(), item.getFile());

			}

		} catch (Exception e) {
			e.printStackTrace();
			DatabaseHandler.close();
			return false;
		}
		DatabaseHandler.close();
		return true;
	}

	/**
	 * Saves the given Supplier into the database, performing an insert
	 * operation if it is a new Supplier, or an update operation if the Supplier
	 * already exists in the database and has been amended
	 * 
	 * @param supplier
	 *            The Supplier object to be saved
	 * @param isNew
	 *            whether the whether the Item is new to the database is new to
	 *            the database
	 * @return Whether successful
	 */
	public static boolean saveSupplier(Supplier supplier, boolean isNew) {
		DatabaseHandler.connect();
		try {
			if (isNew) {
				String query = String.format(
						"insert into supplier values (null,'%s','%s','%s');",
						supplier.getName(), supplier.getCollection(),
						supplier.getAddress());
				DatabaseHandler.doSQL(query);

			} else {
				String query = String
						.format("update Supplier set supplier_name='%s',collection='%s',address='%s' where supplier.pk_supplier_id='%s';",
								supplier.getName(), supplier.getCollection(),
								supplier.getAddress(), supplier.getId());
				DatabaseHandler.doSQL(query);

			}
			DatabaseHandler.close();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			DatabaseHandler.close();
			return false;
		}
	}

	/**
	 * Deletes an Item from the database
	 * 
	 * @param item
	 *            The Item to be deleted
	 * @return Whether successful
	 */
	public static boolean deleteItem(Item item) {

		DatabaseHandler.connect();
		try {
			String query = String.format(
					"delete from item where pk_item_id='%s';", item.getId());

			DatabaseHandler.doSQL(query);
			DatabaseHandler.close();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			DatabaseHandler.close();
			return false;
		}

	}

	/**
	 * Deletes a Supplier from the database. An error message is shown if there
	 * is a problem deleting the Supplier, which is likely due to the foreign
	 * key constraint within the Item table in the database
	 * 
	 * @param sup
	 *            The Supplier to be deleted
	 * @return Whether successful
	 */
	public static boolean deleteSupplier(Supplier sup) {
		DatabaseHandler.connect();
		String query = String.format(
				"delete from supplier where pk_supplier_id='%s';", sup.getId());

		if (DatabaseHandler.doSQL(query)) {
			DatabaseHandler.close();
			return true;
		}

		else {
			JOptionPane
					.showMessageDialog(
							null,
							"Unable to delete Supplier, please ensure no Items in database are from this Supplier");
			DatabaseHandler.close();
			return false;

		}
	}

	/**
	 * Retrieves an image from the Item table, selecting it via the Items id
	 * value. The file is then converted into an ImageIcon object for display
	 * within Java
	 * 
	 * @param id
	 *            The id of the Item that has the required image
	 * @return Whether successful
	 */
	public static ImageIcon getImage(String id) {
		Statement stmt;
		DatabaseHandler.connect();
		id = id.replaceAll("\n", "").trim();
		try {
			stmt = DatabaseHandler.connection.createStatement();

			ResultSet rs = stmt.executeQuery(String.format(
					"select image from item where PK_Item_ID='%s';", id));

			Blob imageBlob;
			byte[] data = null;

			while (rs.next()) {

				imageBlob = rs.getBlob(1);

				data = imageBlob.getBytes(1, (int) imageBlob.length());

			}

			BufferedImage img = ImageIO.read(new ByteArrayInputStream(data));
			ImageIcon ii = new ImageIcon(img);

			DatabaseHandler.close();
			return ii;

		} catch (Exception e) {
			System.out.println("getImage error");
			// e.printStackTrace();
		}
		DatabaseHandler.close();
		return null;

	}

	/**
	 * Inserts an image object into the Item table. The Items id is used to
	 * enter the image into the correct tuple
	 * 
	 * @param id
	 *            The id of the Item in the database
	 * @param image
	 *            The image file to be entered
	 */
	public static void insertImage(String id, File image) {
		try {
			id = id.replaceAll("\n", "").trim();
			DatabaseHandler.connect();
			PreparedStatement psmnt = DatabaseHandler.connection
					.prepareStatement("update item set image=? where item.PK_Item_ID=?");

			psmnt.setString(2, id);
			FileInputStream fis = new FileInputStream(image);
			psmnt.setBinaryStream(1, (InputStream) fis, (int) (image.length()));

			/*
			 * executeUpdate() method execute specified sql query. Here this
			 * query inserts data and image from specified address.
			 */
			int s = psmnt.executeUpdate();
			if (s > 0) {
				System.out.println("Uploaded successfully !");
			} else {
				System.out.println("unable to upload image.");
			}
		} catch (Exception e) {
			// e.printStackTrace();
			System.out.println("Insert Image error");
		}

		DatabaseHandler.close();

	}

}
